package net.web.tool;

import net.web.entity.BlogView;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class DBTool {

    private static Connection conn = null;
    private static ResultSet rs = null;
    private static PreparedStatement pstmt = null;

    public static List<BlogView> selBlogByAll() {
        conn = JdbcTool.getConnection();
        try {
            String sql = "select * from blog where flag=1;";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            List<BlogView> selBlogViewLst = selBlogLst(rs);
            if (selBlogViewLst.isEmpty()) {
                return null;
            }
            return selBlogViewLst;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static BlogView selBlogById(int id) {
        conn = JdbcTool.getConnection();
        try {
            String sql = "select * from blog where id = ?;";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            List<BlogView> selBlogViewLst = selBlogLst(rs);
            if (selBlogViewLst.isEmpty()) {
                return null;
            }
            return selBlogViewLst.get(0);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    private static List<BlogView> selBlogLst(ResultSet rs) throws SQLException {
        List<BlogView> lst = new ArrayList<>();
        while (rs.next()) {
            BlogView blogView = new BlogView();
            blogView.setId(rs.getInt("id"));
            blogView.setTitle(rs.getString("title"));
            blogView.setContent(rs.getString("content"));
            blogView.setExcerpt(rs.getString("excerpt"));
            blogView.setCover_image(rs.getString("coverimage"));
            blogView.setCategory_id(rs.getInt("classid"));
            blogView.setAuthor_id(rs.getInt("authorid"));
            //blogView.setStatus(rs.getString("status"));
            blogView.setIs_featured(rs.getBoolean("isfeatured"));
            blogView.setIs_commentable(rs.getBoolean("iscommentable"));
            blogView.setView_count(rs.getInt("viewcount"));
            blogView.setLike_count(rs.getInt("likecount"));
            blogView.setMeta_title(rs.getString("metatitle"));
            blogView.setMeta_description(rs.getString("metadescription"));
            blogView.setMeta_keywords(rs.getString("metakeywords"));
            blogView.setPublished_at(rs.getString("publishedat"));
            blogView.setCreated_at(rs.getString("createdat"));
            blogView.setUpdated_at(rs.getString("updatedat"));
            blogView.setFlag(rs.getBoolean("flag"));
            lst.add(blogView);
        }
        JdbcTool.close(rs, pstmt, conn);
        return lst;
    }


}
